<?php

namespace app\common\utils;

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use support\Request;
use support\Response;

class Excel
{

    public static function import($keyword = '', $header = [], $list = [])
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $styleArray = [
            'alignment' => [
                'wrapText' => true, // 设置自动换行
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN, // 边框样式，例如Border::BORDER_THICK
                    'color' => ['argb' => 'FF333333'], // 边框颜色，例如红色
                ],
            ],
        ];
        //设置默认宽度
        $sheet->getDefaultColumnDimension()->setWidth(10);
        //独立设置各列
        $sheet->getColumnDimension('A')->setWidth(14);
        $sheet->getColumnDimension('B')->setWidth(32);
        $sheet->getColumnDimension('C')->setWidth(14);
        $sheet->getColumnDimension('D')->setWidth(32);
        $sheet->getColumnDimension('E')->setWidth(32);
        $sheet->getColumnDimension('G')->setWidth(16);
        $sheet->getColumnDimension('H')->setWidth(16);
        $sheet->getColumnDimension('K')->setWidth(12);
        $sheet->getColumnDimension('L')->setWidth(18);
        $sheet->getColumnDimension('M')->setWidth(16);
        $sheet->getColumnDimension('N')->setWidth(16);
        $sheet->getColumnDimension('Q')->setWidth(12);
        $sheet->getColumnDimension('R')->setWidth(16);
        $sheet->getColumnDimension('S')->setWidth(22);
        $sheet->getColumnDimension('T')->setWidth(22);
        $sheet->getColumnDimension('U')->setWidth(16);
        $sheet->getColumnDimension('V')->setWidth(22);
        $sheet->getColumnDimension('W')->setWidth(22);
        $sheet->getColumnDimension('X')->setWidth(16);
        $sheet->getColumnDimension('Y')->setWidth(22);
        $sheet->getColumnDimension('Z')->setWidth(22);
        $sheet->getColumnDimension('AA')->setWidth(26);
        $sheet->getColumnDimension('AB')->setWidth(26);
        $sheet->getColumnDimension('AC')->setWidth(26);
        $sheet->getColumnDimension('AD')->setWidth(26);
        $sheet->getColumnDimension('AE')->setWidth(26);
        $sheet->getColumnDimension('AF')->setWidth(26);
        $sheet->getColumnDimension('AG')->setWidth(26);
        $sheet->getColumnDimension('AG')->setWidth(26);
        $sheet->getColumnDimension('AG')->setWidth(26);
        $sheet->getColumnDimension('AG')->setWidth(26);
        //设置默认行高
        $sheet->getDefaultRowDimension()->setRowHeight(18);
        $sheet->getRowDimension('1')->setRowHeight(80);
        $endRow = self::excelColumnName(count($header)-1);
        $sheet->getStyle("A1:".$endRow."1")->getFont()->setBold(true);
        $sheet->getStyle("A1:".$endRow."999")->getFont()->setSize(10);
        if(count($header) == 36){
            $sheet->getStyle("A1:H1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('9BC2E6');
            $sheet->getStyle("K1:Q1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('9BC2E6');
            $sheet->getStyle("U1:V1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('9BC2E6');
            $sheet->getStyle("X1:Y1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('9BC2E6');
            $sheet->getStyle("AA1:AB1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('9BC2E6');
            $sheet->getStyle("I1:J1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('FFC000');
            $sheet->getStyle("R1:T1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('FFC000');
            $sheet->getStyle("W1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('92D050');
            $sheet->getStyle("Z1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('92D050');
            $sheet->getStyle("AC1:AD1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('92D050');
            $sheet->getStyle("AE1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('FA3534');
            $sheet->getStyle("AF1:AJ1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('9BC2E6');
            
            // $sheet->getStyle("J1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('f4b382');
            // $sheet->getStyle("K1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('ff9900');
            // $sheet->getStyle("L1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('2d529f');
            // $sheet->getStyle("M1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('ff9900');
            // $sheet->getStyle("N1:O1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('2d529f');
            // $sheet->getStyle("P1:Z1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('ff0000');
            // $sheet->getStyle("AA1:AE1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('ff0000');
            // $sheet->getStyle("AD1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('ef949f');
            // $sheet->getStyle("AG1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('fadade');
            // $sheet->getStyle("AH1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('ffff00');
        }
        $sheet->getStyle("A1:".$endRow."999")->getFont()->setSize(10);
        foreach ($header as $key => $value) {
            // $letter = strtoupper(self::numberToLetters($key + 1));
            $letter = self::excelColumnName($key);
            $sheet->setCellValue($letter . '1', $value);
        }
        foreach ($list as $key => $value) {
            $line = $key+2;
            foreach ($value as $k2 => $v2) {
                // $letter = strtoupper(self::numberToLetters($k2 + 1));
                $letter = self::excelColumnName($k2);
                $sheet->setCellValueExplicit($letter.$line, $v2, DataType::TYPE_STRING);
            }
        }
        $sheet->getStyle("A1:".$endRow."".(count($list)+1))->applyFromArray($styleArray);
        $path = '/excel/order_' . time() . '_' . $keyword . '.xlsx';
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $file_path = public_path() . '/upload' . $path;
        $writer->save($file_path);
        return $path;
    }

    public static function importbak($keyword = '', $header = [], $list = [])
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 设置默认行高
        $sheet->getDefaultRowDimension()->setRowHeight(18);
        $sheet->getStyle("A1:J1")->getFont()->setBold(true);
        foreach ($header as $key => $value) {
            $letter = strtoupper(self::numberToLetters($key + 1));
            $sheet->setCellValue($letter . '1', $value);
        }
        foreach ($list as $key => $value) {
            foreach ($value as $k2 => $v2) {
                $letter = strtoupper(self::numberToLetters($k2 + 1));
                $sheet->setCellValueExplicit($letter . ($key + 2), $v2, DataType::TYPE_STRING);
            }
        }

        $path = 'excel/' . time() . '_' . $keyword . '.xlsx';
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $file_path = public_path() . '/upload/' . $path;
        $writer->save($file_path);
        return $path;
    }

    public static function numberToLetters($num)
    {
        $num = $num - 1; // 因为字母'a'的ASCII是97，所以需要减去1
        $letters = 'abcdefghijklmnopqrstuvwxyz';
        return $letters[$num];
    }
    
    public static function excelColumnName($pColumnIndex = 0)
    {
        static $_indexCache= array();
        if (!isset($_indexCache[$pColumnIndex])) {
            if ($pColumnIndex < 26) {
                $_indexCache[$pColumnIndex]= chr(65 + $pColumnIndex);
            }elseif ($pColumnIndex < 702) {
                $_indexCache[$pColumnIndex]= chr(64 + intval($pColumnIndex / 26)). chr(65 + $pColumnIndex % 26);
            }else {
                $_indexCache[$pColumnIndex]= chr(64 + intval(intval($pColumnIndex - 26)/ 676)). chr(65 + ((($pColumnIndex - 26)% 676)/ 26)). chr(65 + $pColumnIndex % 26);
            }
        }
        return $_indexCache[$pColumnIndex];
    }
}